﻿CREATE PROCEDURE dbo.sh_Find
	@Text varchar(8000) = 'Promo'
AS

SET NOCOUNT ON

SELECT  convert(nvarchar(30),sysobjects.name)Name,
				REPLACE(syscomments.text,CHAR(10),CHAR(10)+REPLICATE(' ',31))Text
FROM     syscomments INNER JOIN
               sysobjects ON syscomments.id = sysobjects.id
WHERE  (syscomments.text LIKE N'%'+@Text+'%')
ORDER BY Name

CREATE TABLE #Text (Text varchar(7000))

DECLARE @Find TABLE(Name nvarchar(30),Line varchar(7779))
DECLARE  cur CURSOR READ_ONLY
FOR select name from sysobjects where not xtype IN('F','U','D','S','PK','UQ')

DECLARE @name sysname,@Line varchar(8000)
OPEN cur

FETCH NEXT FROM cur INTO @name
WHILE @@fetch_status = 0 BEGIN
	INSERT INTO #Text	exec sp_helptext @name
	SELECT @Line = Text FROM #Text WHERE Text LIKE '%'+@Text+'%'
	IF @@ROWCOUNT > 0	INSERT INTO @Find VALUES(SUBSTRING(@name,1,30),@Line)
	TRUNCATE TABLE #Text
	FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

SELECT * FROM @Find ORDER BY Name
